import time
import numpy as np
import pymysql
import pandas as pd

class Mysqlutils(object):
    def __init__(self) -> None:
        self.conn = pymysql.connect(
            host= '127.0.0.1',
            user= 'root',
            password= 'root',
            database= 'scenic',
            port= 3306,#明确指定端口
            charset= 'utf8mb4'# 添加字符集设置
        )
    
    def get_data(self):
        cursor = self.conn.cursor(cursor=pymysql.cursors.Dictcursor)
        sql = """
        SELECT t.tourist_agency_name, rel.id_no, LEFT(rel.id_no, 2) as province_code, DAYOFWEEK(gate.create_time) as non_wekend,
        cast(substring(rel.id_no, 7, 4) as unsigned) as birth_year FROM ticket_order_user_rel rel
        JOIN ticket_order t on t.id = rel.order_id
        JOIN order_user_gate_rel gate on gate.ticket_rel_id = rel.id
        WHERE t.tourist_agency_name != '' and t.pay_time != ''
        """
        cursor.execute(sql)
        ret = cursor.fetchall()
        df = pd.DataFrame(ret)
        #print(df.head)
        # 数据清洗
        df['non_weekend'] = df['non_weekend'].apply(lambda x: 1 if x not in [1, 7] else 0) # 非周水
        
        # 新增有效性标记列
        df['valid_id'] = df['id_no'].apply(lambda x : 1 if x and str(x).strip() != "" else 0)
        
        # 计算有记录的老年人及外省游客
        df['elderly_ratio'] = df.apply(lambda x: 1 if (x['valid_id'] and 2025 - x['birth_year'] >= 60) else 0 if x['valid_id'] else np.nan,
            axis=1
        )
        df['out_province_ratio']=df.apply(lambda x : 1 if (x['valid_id'] and x['province_code'] !='44 ')else 0 if x['valid_id']else np.nan,
            axis=1
        )
        
        #分组聚合计算占比
        result = df.groupby(['tourist_agency_name']).agg(
            total_visitors=('id_no','count'),#总游客数
            valid_visitors=('valid_id','sum'),#有效身份证游客数
            out_province=('out_province_ratio','sum'),#外省人数（仅有效id)
            elderly=('elderly_ratio','sum'),#老年人游客人数（仅有效id)
            non_weekend_ratio=('non_weekend','sum'),#非同末占比
        )
        
        #计算实际比例
        result['out_province_ratio'] = result['out_province'] / result['valid_visitors'].replace(0, np.nan)
        result['elderly_ratio'] = result['elderly'] / result['valid_visitors'].replace(0, np.nan)
        
        #清除中间列
        result = result.drop(['out_province','elderly'], axis=1)
        result['out_province_ratio']= result['out_province_ratio'].fillna(0)
        result['elderly_ratio']= result['elderly_ratio'].fillna(0)
        result.to.csv('scenic data.csv')
        
        if __name__== '__main__':
            mu =  Mysqlutils()
            mu.get_data()
